package com.geostar.constructionland.analyse.mapper.statistics;

import com.geostar.constructionland.analyse.entity.vo.ZbqfxTjxx;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface SticZbqfxMapper {

    @Select("       select * from (" +
            "      select 1 lx,t.* from (" +
            "        select xzqhdm,xzqhmc," +
            "      NVL(ROUND(sum(tbdlmj) / 10000, 4),0) dkmj," +
            "      NVL(ROUND(sum(case when gdpdj in ('1','2','3','4','5') then tbdlmj end) / 10000, 4),0) mjxy25," +
            "      nvl(ROUND(sum(case when gdpdj not in ('1','2','3','4','5') then tbdlmj end) / 10000, 4),0) mjdy25," +
            "      nvl(ROUND(sum(case when sfzbq = 'true' then tbdlmj end) / 10000, 4),0) zbqmj," +
            "      nvl(ROUND(sum(case when sfzbq = 'false' then tbdlmj end) / 10000, 4),0) fzbqmj" +
            "      from jsyd_fx_zbqfx where fxbs = #{fxbs} group by xzqhdm,xzqhmc order by xzqhdm" +
            "      ) t" +
            "      union" +
            "      select 2 lx,'汇总' xzqhdm,'-' xzqhmc," +
            "      NVL(ROUND(sum(tbdlmj) / 10000, 4),0) dkmj," +
            "      NVL(ROUND(sum(case when gdpdj in ('1','2','3','4','5') then tbdlmj end) / 10000, 4),0) mjxy25," +
            "      nvl(ROUND(sum(case when gdpdj not in ('1','2','3','4','5') then tbdlmj end) / 10000, 4),0) mjdy25," +
            "      nvl(ROUND(sum(case when sfzbq = 'true' then tbdlmj end) / 10000, 4),0) zbqmj," +
            "      nvl(ROUND(sum(case when sfzbq = 'false' then tbdlmj end) / 10000, 4),0) fzbqmj" +
            "      from jsyd_fx_zbqfx where fxbs = #{fxbs}" +
            "      ) ORDER BY lx")
    List<ZbqfxTjxx> getZbqfxTjxx(String fxbs);
}
